home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
Software Vault: The Diamond Collection
/
The Diamond Collection (Software Vault)(Digital Impact).ISO
/
cdr11
/
ged2ex11.zip
/
GED2EX11.MAC
< prev
Wrap
Text File
|
1995-02-21
|
28KB
|
760 lines
'---------------------- Begin Macro -----------------------------
' ImportGEDCOM - Version 1.1b (Beta)
' (The 'focused' version will be the Beta Carotene version :-) :-)
'
' (c) 1995 by Thomas Edward Thacker, Jr.
'
' Reads a GEnealogical Data COMmunications (GEDCOM) file.
' GEDCOM is a proprietary EDI standard (c) by the LDS Church but
' permitted to be used as an industry standard.
'
' This is part of an ongoing project I have to create a complete
' genealogy system using Microsoft Excel. I thought members of the
' soc.genealogy.computing newsgroup would benefit from receiving an
' early beta version of this piece of the effort for their evaluation
' and enjoyment. Enjoy.
'
' Cover-myself-statement:
'
' (With apologies, I include this to help the newsgroup deter unauthorized
' commercial exploitation of their archives. Fidonet's genealogy archives
' were thusly exploited despite their best effort to stop it.):
'
' Anyone including this routine (or any piece thereof) in any commercial
' package in any way, shape, or form without express written agreement
' implies their agreement to a ten percent royalty of all gross revenues
' and recompense directly or indirectly derived from distribution of this
' product, with no allowable deductions whatsoever. This shall include but
' not be limited to shipping & handling, material, labor, donations, in any
' way shape or form. (That ought to deter them thar software pirates).
'
'
' Hardware - At Least a PC/386/486/585.99998245, MS-Dos 3.3+, 4MB+ RAM.
' Software - Microsoft Windows 3.0 or later
' - Microsoft Excel 5.0 for Windows with OLE extensions.
' (This *won't* work on Excel 4 or earlier)!
' Language - Microsoft Excel 5.0 Visual Basic Macro Language
'
' Developed on a Gateway 2000 P5-60 Pentium,
' Tower Case, 28.8k Faxmodem,
' 8MB Ram, 27MB Swap File, 540MB HD in 4 partitions,
' Free space = C:13,456kB, D:17,884kB, E:44,382kB, F:3,242kB
' Windows in C:\WINDOWS, temp in D:\TEMP
' MS-Dos 6.22, Windows for Workgroups 3.11, Excel 5.0a for Windows,
'
' This has *not* been tested on a MAC. I don't know what changes would
' have to be made to make it MACable. The Excel version required must
' have Excel Visual Basic Macro Language and be able to support Multi
' Sheet Workbooks.
'
' How to Load:
' (1) Edit the text to remove all E-mail headers up to and including
' the "---Begin Source---" line, and all E-mail text after the
' "---End Source---" line.
' (2) Start MS Excel 5.0a to receive the macro.
' (3) On the toolbar, select File, New to start a new spreadsheet.
' (4) Right-click on the Sheet Name (Should be 'Sheet1') to pull up
' the Sheet Menu. Select Insert, Module to create the macro page
' that will receive the source text.
' (5) Select the empty module sheet.
' (6) Select Insert File and give the name of the ASCII text file
' containing this program (GED2GENV.TXT)
' (7) Select File, Save As, GED2GENV.XLS to save this workbook & macro.
'
' How to Use:
' (1) Open workbook GED2GENV.XLS containing this routine. It may
' behoove you to open it as Read Only to keep from bogarting it.
' This is especially true if you have Auto Save turned on.
' (2) Select File, New to open a new Workbook to receive the inbound
' GEDCOM records.
' (3) Select any sheet in the new workbook. Do not open any existing
' Excel workbook already containing any sheets named INDI, FAMI,
' HEAD, or SUBM. If you do so this routine will overwrite the data
' in these sheets.
' (4) Select Tools, Macro and select this routine, Select RUN.
' (5) Enter the path/name of your GEDCOM file.
' (6) Sit back and watch your spreadsheets fill with genealogy. On my
' Pentium system, it proceeds at 200 Individuals + 50 Families per
' minute from a Floppy drive.
' (7) Save your newly tabulated genealogy workbook. Generate tabular
' reports, make charts, ad-nauseum.
'
' Limitations:
' (0) This will *NOT* work in Excel 4 or before. Excel Visual Basic
' doesn't exist until Excel 5.0a. I *don't* want to see 10,000
' "It Doesn't Work!" posts because you tried to use Excel 4 or
' earlier. Upgrade to Excel 5.0a - it's not that expensive. (It
' also puts Excel 4 to shame :-) :-) :-)
' - I also don't want to see 1,000 "Send Mac Version!" messages. I
' have never seen Mac Excel before. In fact, I learned MS Excel for
' the PC by slogging through the Help structure & gleaning what I
' could from the manuals. I imagine not much needs changing except
' perhaps the default file path & file name. If the Mac version
' doesn't yet support Visual Basic Macro Language then complain to
' Microsoft. They're the ones who left the Macs high & dry.
'
' (1) MS Excel's Maximum Sheet Size limits us to 16,383 Individuals and
' 16,383 Families. I have however used Long variables in order to be
' ready for the day when Excel (6? 7?) allows Unlimited Rows (or at
' least 2,147,483,647 rows). Lacking this, version 2 may allow
' multiple Individual and Family sheets. In this case, I1 thru I16000
' would be on INDI1, I16001 to 32000 on INDI2, and so on.
'
' (2) Memory constraints may reduce this further. On a small machine
' Excel yielded up the ghost at around 10,000 persons (Out of Memory).
' A special version I am thinking about would use DDE calls to send
' incoming data along into MS Access.
'
' (3) MS Excel can't handle dates prior to 1900. It forces them to
' character strings. If you try to force a negative Date Serial it
' fills the cell with overflow characters ("#").
'
' Known Problems:
' (1) Case 18 under SayNoMore doesn't work yet. It's supposed to grab a
' pressed break key and ask the user if sie wants to continue but
' the system still grabs the break first. Same effect, but not as
' user-friendly as I would like. (Any System Error is User-Rude).
'
' (2) The box asking for a file name will sometimes not indicate that
' the name entered was a nonexistent file. Instead it will end off
' or generate error 53. Again, same effect. Just re-RUN & give the
' correct name.
'
' (3) Can't get dates prior to 1900 to format as dates. Excel 5.0a
' forces them to character strings. Any attempt to force a negative
' Date Serial Number causes a formatter error (all "#"s). I will
' write a special parse routine that will create a negative Date
' Serial Number and completely overlay that with a format string
' containing the original incoming date. That way Date Sorting will
' yield correct ordering.
'
' (4) To Do List:
' - Write SUBM Interpreter.
' - Write Pre-1900 Date Serial Number Function
' - Include comment records as Cell Notes.
' ----------------------------------------
Dim RECLEVEL As String
Dim LastINDI As String, LastFAMI As String
Dim LastHUSB As String, LastWIFE As String, LastCHIL As String
Dim LastSUBM As String
Dim ZeroMode As String, Level1Mode As String
Dim Level2Mode As String, Level3Mode As String
Dim IndiRow As Long, IndiScroll As Long
Dim FamiRow As Long, FamiScroll As Long
Dim ChildCol As Long
Dim Gender As String
Dim FNUM As Long, GEDREC As String, MoreRecords As Boolean
Dim HeadSheet As Object, SubmSheet As Object
Dim INDISheet As Object, FAMISheet As Object
Dim NbrMales As Long, NbrFemales As Long, NbrBinnaums As Long
Dim NbrChildren As Long, NbrFamilies As Long
Dim CurrChild As Long, HighChild As Long
'
' ImportGEDCOM
'
' Asks for a GEDCOM file name, looks it up, loads it.
'
Sub ImportGEDCOM()
Dim FileOK As Boolean
Dim GEDFileName
GEDFileName = InputBox( _
Title:="GEDCOM to Excel 5.0a Worksheet Import Utility", _
prompt:="Please enter the name of the GEDCOM file:", _
default:="A:\warren.GED")
FileOK = FileExists(GEDFileName)
If FileOK Then
CreateHeading GEDFileName
ParseGEDCOM GEDFileName
Else
MsgBox "File " + GEDFileName + " Doesn't Exist.", _
vbExclamation
End If
End Sub
'
' FileExists
'
' Looks up a file & traps the resulting errors.
'
Function FileExists(FILENAME) As Boolean
On Error GoTo CheckError
FileExists = (Dir(FILENAME) <> "")
Exit Function
CheckError:
Dim Msg
FileExists = False
LastErrNum = Err
Select Case LastErrNum
Case 71 ' DiskNotReady
Msg = "Put a Floppy disk in the drive and close the drive door."
If MsgBox(Msg, vbExclamation + vbOKCancel) = vbOK Then
Resume
Else
Resume Next
End If
Case 68 ' Device Unavailable
Msg = "This drive:\path does not exist: " & FILENAME
MsgBox Msg, vbExclamation
Resume Next
Case Else
Msg = "Unexpected Error #" & Str(Err) & ": " & Error(Err)
MsgBox Msg, vbCritical
End ' Gracefully quit
End Select
End Function
' CreateHeading
'
' Sets up Genvelope Sheet's Headings.
'
Sub CreateHeading(FILENAME)
Set INDISheet = MayBuildSheet("INDI")
ActiveWindow.DisplayGridlines = True
Call MakeHeadingCell("A1", "Self")
Call MakeHeadingCell("B1", "Father")
Call MakeHeadingCell("C1", "Mother")
Call MakeHeadingCell("D1", "Sibling")
Call MakeHeadingCell("E1", "Offspring")
Call MakeHeadingCell("F1", "When Born")
Call MakeHeadingCell("G1", "When Died")
Call MakeHeadingCell("H1", "Sex")
Call MakeHeadingCell("I1", "Birth Surname")
Call MakeHeadingCell("J1", "Birth Given Names")
Call MakeHeadingCell("K1", "Title")
Call MakeHeadingCell("L1", "Birth Place")
Call MakeHeadingCell("M1", "Death Place")
ActiveWindow.DisplayGridlines = True
Sheets("INDI").Select
With ActiveSheet
.Columns("A:H").HorizontalAlignment = xlCenter
.Columns("F:G").NumberFormat = "dd mmm yyyy"
.Range("A1", "E1").Orientation = xlDownward
.Range("H1", "H1").Orientation = xlDownward
With .Rows("1:1")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.RowHeight = 100
.EntireRow.AutoFit
End With
.Columns("I:M").ColumnWidth = 15
.Columns("A:M").EntireColumn.AutoFit
End With
Set FAMISheet = MayBuildSheet("FAMI")
Call MakeHeadingCell("A1", "Family")
Call MakeHeadingCell("B1", "Husband")
Call MakeHeadingCell("C1", "Wife")
Call MakeHeadingCell("D1", "When Married")
Call MakeHeadingCell("E1", "Where Married")
Call MakeHeadingCell("F1", "Divorce")
Call MakeHeadingCell("G1", "1st Child")
Call MakeHeadingCell("H1", "2nd Child")
Call MakeHeadingCell("I1", "3rd Child")
Call MakeHeadingCell("J1", "4th Child")
Call MakeHeadingCell("K1", "5th Child")
Call MakeHeadingCell("L1", "6th Child")
Call MakeHeadingCell("M1", "7th Child")
Call MakeHeadingCell("N1", "8th Child")
Call MakeHeadingCell("O1", "9th Child")
Call MakeHeadingCell("P1", "10th Child")
Call MakeHeadingCell("Q1", "11th Child")
Call MakeHeadingCell("R1", "12th Child")
ActiveWindow.DisplayGridlines = True
Sheets("FAMI").Select
With ActiveSheet
.Columns("A:Z").HorizontalAlignment = xlCenter
.Columns("E:E").HorizontalAlignment = xlLeft
.Columns("D:D").NumberFormat = "dd mmm yyyy"
.Columns("G:Z").ColumnWidth = 5
.Range("A1", "C1").Orientation = xlDownward
.Range("F1", "F1").Orientation = xlDownward
With .Rows("1:1")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.RowHeight = 100
.EntireRow.AutoFit
End With
.Columns("A:Z").EntireColumn.AutoFit
End With
Set HeadSheet = MayBuildSheet("HEAD")
HeadSheet.Columns("A:A").ColumnWidth = 20
Call MakeHeadingCell("A1", "Source")
Call MakeHeadingCell("A2", "Database File")
Call MakeHeadingCell("A3", "Destination")
Call MakeHeadingCell("A4", "Gedcom File")
Call MakeHeadingCell("A5", "Gedcom Date")
Call MakeHeadingCell("A6", "Load Started")
Call MakeHeadingCell("A7", "Load Ended")
Call MakeHeadingCell("A8", "LOAD STATISTICS")
Call MakeHeadingCell("A9", "Persons")
Call MakeHeadingCell("A10", "Males")
Call MakeHeadingCell("A11", "Females")
Call MakeHeadingCell("A12", "Families")
Call MakeHeadingCell("A13", "Children")
Call MakeHeadingCell("A14", "Time to Load")
ActiveWindow.DisplayGridlines = True
Sheets("HEAD").Select
With ActiveSheet
.Range("B9:B13").Value = 0
.Range("C13:V13").Value = 0
.Range("A1:A7").HorizontalAlignment = xlRight
.Range("A9:A14").HorizontalAlignment = xlRight
.Range("B5:B6").NumberFormat = "dd-mmm-yyyy hh:mm:ss"
.Range("B7:B7").NumberFormat = "dd-mmm-yyyy hh:mm:ss"
.Range("B9:B13").NumberFormat = "#,##0"
.Range("B9:B14").HorizontalAlignment = xlLeft
.Range("B6").Value = Now
.Columns("A:B").ColumnWidth = 30
.Columns("A:B").EntireColumn.AutoFit
.Rows("1:16").EntireRow.AutoFit
End With
Set HeadSheet = MayBuildSheet("SUBM")
ActiveWindow.DisplayGridlines = True
End Sub
'
' Selects a sheet & Builds it if it doesn't exist
'
Function MayBuildSheet(Whatever As String) As Object
Dim ChosenSheet As Object
On Error GoTo NoSuchSheet
Sheets(Whatever).Select
Set ChosenSheet = Sheets(Whatever)
Set MayBuildSheet = ChosenSheet
On Error GoTo 0
Exit Function
NoSuchSheet:
On Error GoTo 0
Set ChosenSheet = Sheets.Add
Set MayBuildSheet = ChosenSheet
With ChosenSheet
.Name = Whatever
End With
Sheets(Whatever).Select
End Function
'
' MakeHeadingCell
'
' Edits the Cell Attributes to turn it into a Header Cell.
'
Sub MakeHeadingCell(CellID, Textstring)
Range(CellID).Select
ActiveCell.FormulaR1C1 = "^" + Textstring
With Selection.Font
.Name = "Arial"
.FontStyle = "Normal"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlNone
.ColorIndex = xlAutomatic
End With
With Selection
.WrapText = True
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Borders(xlLeft).LineStyle = xlSingle
.Borders(xlRight).LineStyle = xlSingle
.Borders(xlTop).LineStyle = xlSingle
.Borders(xlBottom).LineStyle = xlSingle
.BorderAround Weight:=xlThin, ColorIndex:=xlAutomatic
.EntireColumn.AutoFit
End With
End Sub
'
' ParseGEDCOM - Reads all GEDCOM records & passes these records
' along to the ParseRecord subroutine which reacts
' to their contents.
'
Sub ParseGEDCOM(FILENAME)
IndiRow = 1
FamiRow = 1
IndiScroll = 1
FamiScroll = 1
NbrMales = 0
NbrFemales = 0
NbrBinnaums = 0
FNUM = FreeFile()
Open FILENAME For Input Access Read As #FNUM
MoreRecords = True
On Error GoTo SayNoMore
Do
Line Input #FNUM, GEDREC
ParseRecord GEDREC
If Not MoreRecords Then Exit Do
Loop While MoreRecords
On Error GoTo 0
Close FNUM
Sheets("HEAD").Select
With ActiveSheet
.Range("B4").Value = FILENAME
.Range("B10").Value = NbrMales
.Range("B11").Value = NbrFemales
.Range("B9").Formula = NbrMales + NbrFemales + NbrBinnaums
.Range("B7").Value = Now
.Range("B14").Value = .Range("B7").Value - .Range("B6").Value
Select Case .Range("B14").Value
Case 0 To 1 / 1440
.Range("B14").NumberFormat = "ss"" sec"""
Case 1 / 1440 To 1 / 24
.Range("B14").NumberFormat = "mm ""min"" ss ""sec"""
Case Else
.Range("B14").NumberFormat = "[hh]:mm:ss"
End Select
.Range("B13").Value = NbrChildren
.Range("B12").Value = NbrFamilies
.Range("B1", "B7").HorizontalAlignment = xlLeft
Columns("B:V").EntireColumn.AutoFit
End With
Exit Sub
SayNoMore:
lasterr = Err
Select Case lasterr
Case 18 ' User Pressed Break Key
If MsgBox("Stop Processing Records?", vbYesNo) = vbNo Then
Resume
Else
MoreRecords = False
End If
Case 62 ' End of File Reached
MoreRecords = False
Case Else ' Unexpected Error
MoreRecords = False
MsgBox Error(lasterr)
End Select
Resume Next
End Sub
'
' ParseRecord - Reacts to the contents of a GEDCOM Record and performs
' actions necessary to glean out field info & insert that
' info into the active sheet.
'
Sub ParseRecord(GEDREC As String)
Dim R As String
Dim LastName As String, FrstName As String, TitlName As String
Dim BirthDate As String, DeathDate As String, MarrDate As String
Dim MarrPlace As String, MarrStatus As String
On Error GoTo MajorBogosity
RECLEVEL = NToken(GEDREC, 1, " ")
Select Case RECLEVEL
Case "0"
ZeroMode = NToken(GEDREC, 3, " ")
If ZeroMode = "" Then ZeroMode = NToken(GEDREC, 2, " ")
If ZeroMode = "" Then ZeroMode = "HEAD"
If ZeroMode = "INDI" Then
LastINDI = NToken(GEDREC, 2, "@")
Sheets("INDI").Select
IndiRow = IndiRow + 1
If IndiRow > 10 Then
ActiveWindow.SmallScroll down:=1
ActiveWindow.LargeScroll toLeft:=1
End If
If IndiRow = 10 Or IndiRow = 20 Or (IndiRow Mod 50) = 0 Then
ActiveSheet.Columns("A:M").EntireColumn.AutoFit
End If
ActiveSheet.Cells(IndiRow, 1).Formula = LastINDI
ElseIf ZeroMode = "FAM" Then
LastFAMI = NToken(GEDREC, 2, "@")
If FamiRow = 1 Then
Sheets("INDI").Select
ActiveSheet.Columns("A:L").EntireColumn.AutoFit
End If
Sheets("FAMI").Select
FamiRow = FamiRow + 1
NbrFamilies = NbrFamilies + 1
If FamiRow > 10 Then
ActiveWindow.SmallScroll down:=1
ActiveWindow.LargeScroll toLeft:=1
End If
If FamiRow = 10 Or FamiRow = 20 Or (FamiRow Mod 50) = 0 Then
ActiveSheet.Columns("A:Z").EntireColumn.AutoFit
End If
ChildCol = 7
CurrChild = 0
ActiveSheet.Cells(FamiRow, 1).Formula = LastFAMI
LastHUSB = ""
LastWIFE = ""
LastCHIL = ""
ElseIf ZeroMode = "HEAD" Then
Sheets("HEAD").Select
Range("A2").Select
ElseIf ZeroMode = "SUBM" Then
Sheets("SUBM").Select
Range("A2").Select
ElseIf ZeroMode = "TRLR" Then
Sheets("FAMI").Select
Columns("A:Z").EntireColumn.AutoFit
Sheets("INDI").Select
Columns("A:M").EntireColumn.AutoFit
Else
ZeroMode = "none"
End If
Case "1"
If ZeroMode = "FAM" Then
Level1Mode = NToken(GEDREC, 2, " ")
If Level1Mode = "HUSB" Then
LastHUSB = NToken(GEDREC, 2, "@")
If LastHUSB <> "I0" And LastHUSB <> "0" Then
ActiveSheet.Cells(FamiRow, 2).Value = LastHUSB
End If
ElseIf Level1Mode = "WIFE" Then
LastWIFE = NToken(GEDREC, 2, "@")
If LastWIFE <> "I0" And LastWIFE <> "0" Then
ActiveSheet.Cells(FamiRow, 3).Value = LastWIFE
End If
ElseIf Level1Mode = "CHIL" Then
LastCHIL = NToken(GEDREC, 2, "@")
If LastCHIL <> "I0" And LastCHIL <> "0" Then
ActiveSheet.Cells(FamiRow, ChildCol).Value = LastCHIL
Link_Family
ChildCol = ChildCol + 1
CurrChild = CurrChild + 1
NbrChildren = NbrChildren + 1
If CurrChild > HighChild Then HighChild = CurrChild
With Sheets("HEAD")
With .Cells(13, CurrChild + 2)
.Value = .Value + 1
End With
.Cells(12, CurrChild + 2).Value = CurrChild
End With
End If
ElseIf Level1Mode = "MARR" Then
End If
ElseIf ZeroMode = "INDI" Then
Level1Mode = NToken(GEDREC, 2, " ")
If Level1Mode = "NAME" Then
R = Mid(GEDREC, 8)
LastName = NToken(R, 2, "/")
FrstName = NToken(R, 1, "/")
TitlName = NToken(R, 3, "/")
ActiveSheet.Cells(IndiRow, 9).Formula = LastName
ActiveSheet.Cells(IndiRow, 10).Formula = FrstName
ActiveSheet.Cells(IndiRow, 11).Formula = TitlName
ElseIf Level1Mode = "TITL" Then
R = Mid(GEDREC, 8)
TitlName = Trim(R)
ActiveSheet.Cells(IndiRow, 11).Formula = TitlName
ElseIf Level1Mode = "SEX" Then
R = Mid(GEDREC, 7)
Gender = NToken(R, 1, " ")
If Gender = "M" Then NbrMales = NbrMales + 1
If Gender = "F" Then NbrFemales = NbrFemales + 1
If Gender = "B" Then NbrBinnaums = NbrBinnaums + 1 ' :-) :-) :-)
ActiveSheet.Cells(IndiRow, 8).Formula = Gender
ElseIf Level1Mode = "BIRT" Then
ElseIf Level1Mode = "BAPM" Then
ElseIf Level1Mode = "DEAT" Then
ElseIf Level1Mode = "BURI" Then
End If
ElseIf ZeroMode = "HEAD" Then
Level1Mode = NToken(GEDREC, 2, " ")
Debug.Print GEDREC
If Level1Mode = "SOUR" Then
R = Mid(GEDREC, 7)
ActiveSheet.Cells(1, 2).Value = Trim(R)
ElseIf Level1Mode = "DEST" Then
R = Mid(GEDREC, 7)
ActiveSheet.Cells(3, 2).Value = Trim(R)
ElseIf Level1Mode = "DATE" Then
R = Mid(GEDREC, 7)
With ActiveSheet.Cells(5, 2)
.NumberFormat = "dd mmm yyyy"
.Formula = Trim(R)
End With
ElseIf Level1Mode = "FILE" Then
R = Mid(GEDREC, 7)
ActiveSheet.Cells(2, 2).Value = Trim(R)
End If
ElseIf ZeroMode = "SUBM" Then
Level1Mode = NToken(GEDREC, 2, " ")
R = Mid(GEDREC, 7)
LastSUBM = NToken(GEDREC, 2, "@")
End If
Case "2"
Level2Mode = NToken(GEDREC, 2, " ")
R = Mid(GEDREC, 4 + Len(Level2Mode))
If ZeroMode = "INDI" Then
If Level1Mode = "BIRT" Then
If Level2Mode = "DATE" Then
BirthDate = Trim(R)
With ActiveSheet.Cells(IndiRow, 6)
If IsNumeric(BirthDate) Then
.NumberFormat = """ABT ""yyyy"
.Value = CDate("1/1/" & BirthDate)
Else
.NumberFormat = "dd mmm yyyy"
.Value = BirthDate
End If
End With
ElseIf Level2Mode = "PLAC" Then
BirthPlace = Trim(R)
ActiveSheet.Cells(IndiRow, 12).Value = BirthPlace
End If
ElseIf Level1Mode = "DEAT" Then
If Level2Mode = "DATE" Then
DeathDate = Trim(R)
With ActiveSheet.Cells(IndiRow, 7)
If IsNumeric(DeathDate) Then
.NumberFormat = """ABT ""yyyy"
.Value = CDate("1/1/" & DeathDate)
Else
.NumberFormat = "dd mmm yyyy"
.Value = DeathDate
End If
End With
ElseIf Level2Mode = "PLAC" Then
DeathPlace = Trim(R)
ActiveSheet.Cells(IndiRow, 13).Value = DeathPlace
End If
End If
ElseIf ZeroMode = "FAM" Then
If Level1Mode = "MARR" Then
If Level2Mode = "DATE" Then
MarrDate = Trim(R)
With ActiveSheet.Cells(FamiRow, 4)
If IsNumeric(MarrDate) Then
.NumberFormat = """ABT ""yyyy"
.Value = CDate("1/1/" & MarrDate)
Else
.NumberFormat = "dd mmm yyyy"
.Value = MarrDate
End If
End With
ElseIf Level2Mode = "PLAC" Then
MarrPlace = Trim(R)
ActiveSheet.Cells(FamiRow, 5).Value = MarrPlace
ElseIf Level2Mode = "DIV" Then
MarrStatus = Trim(R)
ActiveSheet.Cells(FamiRow, 6).Value = MarrStatus
End If
End If
End If
Case Else
End Select
Exit Sub
MajorBogosity:
lasterr = Err
lasterrln = errl
With Sheets("HEAD")
.Cells(27, 1).Value = lasterr
.Cells(28, 1).Value = lasterrln
.Cells(29, 1).Value = Error(lasterr)
.Cells(30, 1).Value = GEDREC
End With
Resume Next
End Sub
'
' Link_Families - Sweeps down FAMI & builds up the Father, Mother,
' Sibling, and Offspring linkages.
'
' Called during FAMI import when a CHILd is encountered.
'
Sub Link_Family()
Dim FathrRow As Long, MothrRow As Long
Dim ChildRow As Long
Dim Sibling As String, SiblingRow As Long
Dim Offspring As String, OffspringRow As Long
Dim InsertingSibling As Boolean
With INDISheet.Columns("A:A")
ChildRow = .Find(LastCHIL).Row
FathrRow = .Find(LastHUSB).Row
MothrRow = .Find(LastWIFE).Row
End With
If FathrRow <> 0 Then
With INDISheet.Cells(ChildRow, 2)
If .Value = "" Then .Value = LastHUSB
End With
With INDISheet.Cells(FathrRow, 5)
If .Value = "" Then .Value = LastCHIL
End With
With INDISheet
InsertingSibling = True
Offspring = .Cells(FathrRow, 5).Value
If Offspring = LastCHIL Then InsertingSibling = False
OffspringRow = .Columns("A:A").Find(Offspring).Row
If OffspringRow = 0 Then InsertingSibling = False
Do While InsertingSibling
Sibling = .Cells(OffspringRow, 4).Value
If Sibling = "" Then
.Cells(OffspringRow, 4).Value = LastCHIL
Exit Do
End If
If Sibling = LastCHIL Then Exit Do
Offspring = Sibling
OffspringRow = .Columns("A:A").Find(Offspring).Row
If OffspringRow = 0 Then Exit Do
Loop
End With
End If
If MothrRow <> 0 Then
With INDISheet.Cells(ChildRow, 3)
If .Value = "" Then .Value = LastWIFE
End With
With INDISheet.Cells(MothrRow, 5)
If .Value = "" Then .Value = LastCHIL
End With
With INDISheet
InsertingSibling = True
Offspring = .Cells(MothrRow, 5).Value
If Offspring = LastCHIL Then InsertingSibling = False
OffspringRow = .Columns("A:A").Find(Offspring).Row
If OffspringRow = 0 Then InsertingSibling = False
Do While InsertingSibling
Sibling = .Cells(OffspringRow, 4).Value
If Sibling = "" Then
.Cells(OffspringRow, 4).Value = LastCHIL
Exit Do
End If
If Sibling = LastCHIL Then Exit Do
Offspring = Sibling
OffspringRow = .Columns("A:A").Find(Offspring).Row
If OffspringRow = 0 Then Exit Do
Loop
End With
End If
End Sub
'
' NToken - returns the nth token in a delimited string.
' - What: The string to be parsed for tokens
' - Which: The token number to look for
' - Delims: The set of token delimiters
' Example: S=NToken("1 NAME Tom /Thacker/",2,"/") returns "Thacker".
'
Function NToken(What As String, _
Which As Integer, _
Delims As String) As String
Dim Work As String
Dim I As Integer, Hits As Integer
Dim Before As Integer, After As Integer
Work = Trim(What) + Delims ' Ensure that last token gets gotten
For I = 1 To Len(Work)
If POS(Delims, Mid(Work, I, 1)) <> 0 Then
Hits = Hits + 1
If Hits = (Which - 1) Then Before = I
If Hits = Which Then After = I
End If
Next I
NToken = ""
If Before = 0 And After = 0 Then Exit Function
If (After - Before) < 2 Then Exit Function
NToken = Mid(Work, Before + 1, After - Before - 1)
End Function
'
' POS takes the place of the missing string POS function
' found in every Basic on Earth EXCEPT MS EXCEL BASIC.
'
Function POS(Target As String, _
Source As String) As Integer
Dim I As Integer, TargLen As Integer, SrcLen As Integer
TargLen = Len(Target)
SrcLen = Len(Source)
For I = 1 To TargLen - SrcLen + 1
If Mid(Target, I, SrcLen) = Source Then
POS = I
Exit Function
End If
Next I
POS = 0
End Function